package com.deductive.service.special.special_import.service;

import com.alibaba.fastjson2.JSONObject;

import com.deductive.mapper.PgDBMapper;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import java.util.ArrayList;
import java.util.LinkedHashSet;
import java.util.List;


@Service
@Slf4j
public class PgServiceImpl {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Autowired
    private PgDBMapper pgDBMapper;


    public JdbcTemplate jdbcTemplate() {
        return jdbcTemplate;
    }


    public void updateTable(String tableName) {
        List<JSONObject> columns = pgDBMapper.getColumns(tableName);
        for (JSONObject column : columns) {

            String column_name = column.getString("column_name");

            String col_name = column.getString("column_name");
            if (col_name.contains("—")) {
                col_name = col_name.replace("—", "_").trim();
            }
            if (col_name.contains("、")) {
                col_name = col_name.replace("、", "_").trim();
            }
            if (col_name.contains("（") || col_name.contains("(")) {
                col_name = col_name.replace("（", "_").replace("(", "_").replace("）", "").replace(")", "").trim();
            }
            if (col_name.contains("/") || col_name.contains("\\")) {
                col_name = col_name.replace("/", "或").replace("\\", "或").trim();
            }
            col_name = col_name.trim();
//            pgDBMapper.updateColumn(tableName, column_name, col_name);
            if (column_name.equalsIgnoreCase("OBJECTID") || column_name.equalsIgnoreCase("GID")) {
                pgDBMapper.updateColumn(tableName, column_name, "id");
            }
            if (column_name.equalsIgnoreCase("USERID")) {
                pgDBMapper.deleteColumnByTable(tableName, column_name);
            }
            if (column_name.equalsIgnoreCase("smuserid")) {
                pgDBMapper.deleteColumnByTable(tableName, column_name);
            }
            if (column_name.contains("　")){
                String newName = column_name.replaceAll("　", "");
                pgDBMapper.updateColumn(tableName, column_name, newName);
            }

        }
    }


    public void combinedMeter(String tableNames, String newTableName) {
        LinkedHashSet<JSONObject> hashSet = new LinkedHashSet<>();
        String[] split = tableNames.split(",");
        for (String tableName : split) {
            List<JSONObject> columns = pgDBMapper.getColumns(tableName);
            hashSet.addAll(columns);
        }

        List<JSONObject> list = new ArrayList<>(hashSet);
        int wjlIndexOf = 0;
        int nameIndexOf = 0;
        int zbsjIndexOf = 0;
        JSONObject nameJson = null;
        JSONObject zbsjJson = null;
        for (int i = 0; i < list.size(); i++) {
            JSONObject jsonObject = list.get(i);
            String columnName = jsonObject.getString("column_name");
            if (columnName.equals("五级类")) {
                wjlIndexOf = i;
            }
            if (columnName.equals("名称")) {
                nameIndexOf = i;
                nameJson = jsonObject;
            }
            if (columnName.equals("整编时间")) {
                zbsjIndexOf = i;
                zbsjJson = jsonObject;
            }
        }

        if (nameIndexOf != 0) {
            list.remove(nameIndexOf);
            list.add(wjlIndexOf + 1, nameJson);
        }
        if (zbsjIndexOf != 0) {
            list.remove(zbsjIndexOf);
            list.add(zbsjJson);
        }


        StringBuilder builder = new StringBuilder();
        for (JSONObject jsonObject : list) {
            String columnName = jsonObject.getString("column_name");
            String udtName = jsonObject.getString("udt_name");
            String characterMaximumLength = jsonObject.getString("character_maximum_length");
            if (!columnName.equals("id") && !columnName.equals("geom")) {
                builder.append(" \"").append(columnName).append("\" ").append(udtName);
                if (characterMaximumLength != null) {
                    builder.append("(").append(characterMaximumLength).append(")");
                }
                builder.append(",\n");
            }
        }
        builder.setLength(builder.length() - 2);
//        String newTableName = split[0].substring(0, split[0].length() - 1);
        String tableSql = createTableSql(newTableName, builder.toString());
        pgDBMapper.insertOne(tableSql);
        log.info(tableSql);
        for (String tableName : split) {
            StringBuilder sb = new StringBuilder();
            List<JSONObject> columns = pgDBMapper.getColumns(tableName);
            for (JSONObject column : columns) {
                String columnName = column.getString("column_name");
                if (!columnName.equals("id")) {
                    sb.append(" \"").append(columnName).append("\" ").append(",\n");
                }
            }
            sb.setLength(sb.length() - 2);
            String insertSql = "INSERT INTO " + newTableName + "(" + sb + ")" + " select " + sb + " from " + tableName;
            pgDBMapper.insertOne(insertSql);
        }


    }

    public String createTableSql(String tableName, String columnName) {
        String sql = String.format("DROP TABLE IF EXISTS \"public\".\"%s\";\n" +
                "CREATE TABLE \"public\".\"%s\" (\n" +
                "   \"id\" int8 NOT NULL GENERATED ALWAYS AS IDENTITY (\n" +
                "INCREMENT 1\n" +
                "MINVALUE  1\n" +
                "MAXVALUE 9223372036854775807\n" +
                "START 1\n" +
                "CACHE 1\n" +
                "),\n" +
                "  \"geom\" geometry(GEOMETRY, 4490),\n" +
                "  %s\n" +
                ")\n" +
                ";\n" +
                "\n" +
                "ALTER TABLE \"public\".\"%s\" ADD CONSTRAINT \"%s_pkey\" PRIMARY KEY (\"id\");\n", tableName, tableName, columnName, tableName, tableName);
        return sql;
    }
}
